package org.spider.core.service;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.AES;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.IService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.spider.api.domain.vo.DatasourceVo;
import org.spider.api.domain.model.DataSourceModel;
import org.spider.api.domain.vo.TableVo;
import org.spider.core.mapper.DatasourceMapper;
import org.spider.core.utils.DataSourceUtils;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author 20384
 * @description 针对表【datasource】的数据库操作Service实现
 * @createDate 2024-02-07 19:32:54
 */
@Service
public class DatasourceService extends ServiceImpl<DatasourceMapper, DataSourceModel>
        implements IService<DataSourceModel> {
    private static final String AES_KEY = "daijunjie_biyela";
    private static final Logger logger = LoggerFactory.getLogger(DatasourceService.class);

    @Resource
    private DatasourceMapper datasourceMapper;

    public IPage<DataSourceModel> selectPages(Page<DataSourceModel> page, String name) {
        LambdaQueryWrapper<DataSourceModel> wrapper = new LambdaQueryWrapper<>();
        //name是别名
        wrapper.like(StringUtils.isNotEmpty(name), DataSourceModel::getName, name)
                .orderByDesc(DataSourceModel::getCreatDatetime);
        Page<DataSourceModel> resPage = this.page(page, wrapper);
        //AES解密
        resPage.getRecords().forEach(ds ->
                ds.setPassword(AES.decrypt(ds.getPassword(), AES_KEY)));
        return resPage;
    }

    public List<DatasourceVo> listAll() {
        return datasourceMapper.listAll();
    }

    public DataSourceModel getById(Serializable id) {
        DataSourceModel byId = super.getById(id);
        if (byId != null) {
            byId.setPassword(AES.decrypt(byId.getPassword(), AES_KEY));
        }
        return byId;
    }

    public boolean updateById(DataSourceModel ds) {
        ds.setPassword(AES.encrypt(ds.getPassword(), AES_KEY));
        return super.updateById(ds);
    }

    public boolean save(DataSourceModel ds) {
        ds.setPassword(AES.encrypt(ds.getPassword(), AES_KEY));
        return super.save(ds);
    }

    public List<String> listTables(String datasourceId) {
        List<String> tableNames = new ArrayList<>();
        DataSource dataSource = DataSourceUtils.getDataSource(datasourceId);
        try (Connection connection = dataSource.getConnection()) {
            String catalog = connection.getCatalog();
            String schema = connection.getSchema();
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet res = metaData.getTables(catalog, schema, "%"
                    , new String[]{"TABLE"});
            //  获取所有的表名
            while (res.next()) {
                tableNames.add(res.getString("TABLE_NAME"));
            }
            res.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
        return tableNames;
    }

    public List<Map<String, Object>> listTableDetails(String datasourceId) {
        List<String> tableNames = new ArrayList<>();
        List<Map<String, Object>> tableFieldProperties = new ArrayList<>();
        DataSource dataSource = DataSourceUtils.getDataSource(datasourceId);
        try (Connection connection = dataSource.getConnection()) {
            String catalog = connection.getCatalog();
            String schema = connection.getSchema();
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet res = metaData.getTables(catalog, schema, "%"
                    , new String[]{"TABLE"});
            //  获取所有的表名
            while (res.next()) {
                tableNames.add(res.getString("TABLE_NAME"));
            }
            res.close();
            // 遍历素有的表名，获取其字段
            for (String table : tableNames) {
                ResultSet rs = metaData.getColumns(catalog, schema, table, "%");
                Map<String, Object> mmp = new HashMap<>();
                while (rs.next()) {
                    // 遍历每个字段，封装字段的各个属性
                    Map<String, String> map = new HashMap<>();
                    map.put("column_name", rs.getString("COLUMN_NAME"));
                    map.put("table_name", rs.getString("TYPE_NAME"));
                    map.put("data_size", rs.getString("COLUMN_SIZE"));
                    map.put("decimal_digits", rs.getString("DECIMAL_DIGITS"));
                    map.put("nullable", rs.getString("NULLABLE"));
                    map.put("remarks", rs.getString("REMARKS"));
//                    int numPrecRadix = rs.getInt("NUM_PREC_RADIX");
//                    logger.info("table:{}  ,column: {}, fields: {}",table,map.get("column_name"),map);
                    mmp.put(map.get("column_name"), map);
                }
                logger.info("table: {} , column properties:{}", table, mmp);
                mmp.put("__table_name", table);
                tableFieldProperties.add(mmp);
                rs.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return tableFieldProperties;
    }

    public List<Map<String, String>> tableDetails(String datasourceId, String table) {
        List<Map<String, String>> tableFieldProperties = new ArrayList<>();
        DataSource dataSource = DataSourceUtils.getDataSource(datasourceId);
        try (Connection connection = dataSource.getConnection()) {
            String catalog = connection.getCatalog();
            String schema = connection.getSchema();
            DatabaseMetaData metaData = connection.getMetaData();
            // 遍历指定的表名，获取其字段
            ResultSet rs = metaData.getColumns(catalog, schema, table, "%");
            while (rs.next()) {
                // 遍历每个字段，封装字段的各个属性
                Map<String, String> map = new HashMap<>();
                map.put("column_name", rs.getString("COLUMN_NAME"));
                map.put("type_name", rs.getString("TYPE_NAME"));
                map.put("data_size", rs.getString("COLUMN_SIZE"));
                map.put("decimal_digits", rs.getString("DECIMAL_DIGITS"));
                map.put("nullable", rs.getString("NULLABLE"));
                map.put("remarks", rs.getString("REMARKS"));
                tableFieldProperties.add(map);
            }
            rs.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return tableFieldProperties;
    }
}




